﻿Imports System.IO
Imports System.Data.SQLite
Imports System.Text.RegularExpressions

Public Class XMLOps

    Private _filepath As String
    Private _DS As DataSet

    Property Filepath() As String
        Get
            Return _filepath
        End Get
        Set(value As String)
            _filepath = value
        End Set
    End Property

    Property Dataset() As DataSet
        Get
            Return _DS
        End Get
        Set(value As DataSet)
            _DS = value
        End Set
    End Property

    Public Function ImportXML() As Boolean
        GameSetMatch.OperationLB.Text = "Importing XML data file..."
        Application.DoEvents()
        Try
            _DS.ReadXml(_filepath, XmlReadMode.Auto)
        Catch ex As Exception
            MsgBox("There was an error whislt reading the xml file:" & vbCr & ex.Message)
            Return False

        End Try

        Return True
    End Function

    Public Function ExportXML()
        GameSetMatch.OperationLB.Text = "Exporting game data file..."
        Application.DoEvents()
        _DS.WriteXml(_filepath, XmlWriteMode.WriteSchema)
        Return True
    End Function

    Public Function ImportGEDb3(Filepath As String, RecDT As DataTable, FileDupes As DataTable, FilenameCol As String) As Boolean
        GameSetMatch.OperationLB.Text = "Importing .db3 data file..."
        Application.DoEvents()

        Debug.WriteLine(vbCr & "========== IMPORT GE DB3 ==================")

        GameSetMatch.log.Log_Data("Starting IMport db3 proceedure, with variables:", "Import process started")
        GameSetMatch.log.Log_Data("Filepath: " & Filepath)
        GameSetMatch.log.Log_Data("RecDT: " & RecDT.ToString)
        GameSetMatch.log.Log_Data("FileDupes: " & FileDupes.ToString)
        GameSetMatch.log.Log_Data("FilenameCol: " & FilenameCol)

        'Load DT with raw data:
        Dim dt As New DataTable
        Try
            Dim cnn As New SQLiteConnection("Data Source='" & Filepath & "'")
            cnn.Open()
            Dim mycommand As New SQLiteCommand(cnn)
            mycommand.CommandText = "Select * from GameData"
            Dim reader As SQLiteDataReader = mycommand.ExecuteReader()
            dt.Load(reader)
            reader.Close()
            cnn.Close()

            ' Catching exceptions is for communists
        Catch ex As Exception
            GameSetMatch.log.Log_Error("whislt trying to load GSM with db3 info: " & ex.Message)
            GameSetMatch.log.Log_Error("Further: " & ex.InnerException.ToString)
            Return Nothing
        End Try

        GameSetMatch.log.Log_Data(".db3 loaded. No. records: " & dt.Rows.Count.ToString)


        'Process raw dt
        Dim count As Integer = 0
        GameSetMatch.OperationLB.Text = "Processing GameEx .db3 file...."
        GameSetMatch.PB1.Minimum = 0
        GameSetMatch.PB1.Maximum = dt.Rows.Count
        Application.DoEvents()

        Dim Clones As New List(Of KeyValuePair(Of String, String))
        Clones.Clear()

        Dim SystemRow() As DataRow
        Dim SystemID As Integer
        SystemRow = GameSetMatch._GSMDS.Tables("Systems").Select("SystemName='GameEx'")
        SystemID = SystemRow(0).Item("ID")

        Dim newrow As DataRow

        Debug.WriteLine("Starting Games Read...")

        RecDT.BeginLoadData()

        For Each row As DataRow In dt.Rows
            'If (IsDBNull(row.Item("CloneOf"))) Then
            '    ' No cloneOf or RomOf
            '    Debug.WriteLine("Not Clone. Name: {0} ; Filename: {1}",
            '                    row.Item("description"),
            '                    row.Item("name"))

            newrow = RecDT.NewRow
            newrow("GameName") = RemoveBracketedText(row.Item("Name"), My.Settings.RemBracksGamename)
            newrow("SafeGameName") = SafeFilename(RemoveBracketedText(row.Item("Name").ToString, My.Settings.RemBracksGamename))
            ' newrow("PrimaryFilename") = row.Item("GameBase")
            'Try
            newrow("PrimaryFilename") = RemoveBracketedText(row.Item(FilenameCol).ToString, My.Settings.RemBracksFilename)
            'removed "RemoveBracketedText"
            'Catch
            '    MessageBox.Show("There appears to be a problem with the db column name enetered for this setup. Please revise. Column name as stands: " & FilenameCol, _
            '            "Error importing game data", _
            '            MessageBoxButtons.OK, _
            '            MessageBoxIcon.Exclamation, _
            '            MessageBoxDefaultButton.Button1)
            '    Return False
            'End Try

            Debug.WriteLine("Processing game: " & row.Item("Name"))

            newrow("SystemID") = SystemID

            ' If row.Item("Name") = "1Xtreme" Then
            RecDT.Rows.Add(newrow)    'THIS THE ONE SLOWING IT DOWN ON SECOND PASS
            ' End If


            'Else

            'Process any clones...

            If Not (IsDBNull(row("GameBase"))) And (FilenameCol <> "GameBase") Then
                Clones.Add(New KeyValuePair(Of String, String)(row.Item("GameBase"), "GameBase"))
            End If

            If Not (IsDBNull(row("HyperList"))) And (FilenameCol <> "HyperList") Then
                Clones.Add(New KeyValuePair(Of String, String)(row.Item("HyperList"), "HyperList"))
            End If

            If Not (IsDBNull(row("GoodName"))) And (FilenameCol <> "GoodName") Then
                Clones.Add(New KeyValuePair(Of String, String)(row.Item("GoodName"), "GoodName"))
            End If

            If Not (IsDBNull(row("NoIntro"))) And (FilenameCol <> "NoIntro") Then
                Clones.Add(New KeyValuePair(Of String, String)(row.Item("NoIntro"), "NoIntro"))
            End If

            If Not (IsDBNull(row("TOSEC"))) And (FilenameCol <> "TOSEC") Then
                Clones.Add(New KeyValuePair(Of String, String)(row.Item("TOSEC"), "TOSEC"))

            End If

            If Not (IsDBNull(row("ReDump"))) And (FilenameCol <> "ReDump") Then

                Clones.Add(New KeyValuePair(Of String, String)(row.Item("ReDump"), "ReDump"))

            End If

            count = count + 1
            GameSetMatch.PB1.Value = count
            Application.DoEvents()

        Next

        RecDT.EndLoadData()

        GameSetMatch.log.Log_Data("Records transferred from raw to GSM datatable: " & count.ToString)
        GameSetMatch.log.Log_Data("NUmber of dupes: " & Clones.Count.ToString)

        ' transfer Dupes dictionary to datatable

        count = 0
        GameSetMatch.OperationLB.Text = "Processing GameEx .db3 Clones..."
        GameSetMatch.PB1.Minimum = 0
        GameSetMatch.PB1.Maximum = Clones.Count
        Application.DoEvents()

        Dim GameRow() As DataRow
        Dim GameID As Integer
        For Each kvp As KeyValuePair(Of String, String) In Clones
            newrow = FileDupes.NewRow
            GameRow = RecDT.Select("PrimaryFilename='" & ReplaceEscapeChars(kvp.Key) & "'")
            If GameRow.Length > 0 Then
                GameID = GameRow(0).Item("ID")
                newrow("GamesID") = GameID
                newrow("Filename") = kvp.Key
                newrow("Standard") = kvp.Value
                FileDupes.Rows.Add(newrow)
            Else
                Debug.WriteLine("Error: No Game with Primary Filename: " & kvp.Key)
            End If

            count = count + 1
            GameSetMatch.PB1.Value = count
            Application.DoEvents()

        Next

        'GameSetMatch.log.Log_Data("Final number or records trasferred: " & count.ToString)
        GameSetMatch.log.Log_Data("Done", "Import process finished")

        Return True
    End Function

    Public Function RemoveBracketedText(Str As String, Optional Use As Boolean = True) As String
        If Use = False Then Return Str
        Str = Regex.Replace(Str, "\[(.*?)\]", "")
        Str = Regex.Replace(Str, "\((.*?)\)", "")
        Str = Str.Trim()
        Return Str

    End Function


    Public Shared Function NotNull(Of T)(ByVal Value As T, ByVal DefaultValue As T) As T

        If Value Is Nothing OrElse IsDBNull(Value) Then
            Return DefaultValue
        Else
            Return Value
        End If
    End Function

    Public Shared Function ReplaceEscapeChars(str As String) As String
        'If the string is null
        If str Is Nothing Then
            Return str
        End If

        'If the string is empty
        If str = "" Then
            Return str
        End If

        'Replaces single quote (') with two (2) single quotes ('')
        'solves the problem of inserting, updating or selecting a text with single quote (')
        'i.e.: Cox's Bazar, World's economy etc.
        str = str.Replace("'", "''")
        Return str
    End Function


    Public Function TransferMame(MameDS As DataSet, RecDT As DataTable, FileDupes As DataTable)

        Dim Clones As New Dictionary(Of String, String)
        Clones.Clear()

        Dim SystemRow() As DataRow
        Dim SystemID As Integer
        SystemRow = GameSetMatch._GSMDS.Tables("Systems").Select("SystemName='MAME'")
        SystemID = SystemRow(0).Item("ID")

        Dim newrow As DataRow

        Dim count As Integer = 0
        GameSetMatch.OperationLB.Text = "Processing mame.dat file...."
        GameSetMatch.PB1.Minimum = 0
        GameSetMatch.PB1.Maximum = MameDS.Tables("game").Rows.Count
        Application.DoEvents()


        For Each row As DataRow In MameDS.Tables("game").Rows
            If (IsDBNull(row.Item("cloneof"))) And (IsDBNull(row.Item("romof"))) Then
                ' No cloneOf or RomOf
                Debug.WriteLine("Not Clone. Name: {0} ; Filename: {1}",
                                row.Item("description"),
                                row.Item("name"))

                newrow = RecDT.NewRow
                newrow("GameName") = row.Item("description")
                newrow("PrimaryFilename") = row.Item("name")
                newrow("SystemID") = SystemID
                RecDT.Rows.Add(newrow)
            Else
                'CloneOf or RomOf exists
                If Not (IsDBNull(row.Item("cloneof"))) Then
                    If Clones.ContainsKey(row.Item("name")) Then
                        'clone name already in dictioanry = do nothing
                    Else
                        Clones.Add(row.Item("name"), row.Item("cloneof"))
                    End If
                End If

                If Not (IsDBNull(row.Item("romof"))) Then
                    If Clones.ContainsKey(row.Item("name")) Then
                        'clone name already in dictioanry = do nothing
                    Else
                        Clones.Add(row.Item("name"), row.Item("romof"))
                    End If
                End If
            End If

            count = count + 1
            GameSetMatch.PB1.Value = count
            Application.DoEvents()

        Next

        'transfer Dupes disctionary to datatable

        count = 0
        GameSetMatch.OperationLB.Text = "Processing mame.dat Clones and DupeRoms..."
        GameSetMatch.PB1.Minimum = 0
        GameSetMatch.PB1.Maximum = Clones.Count
        Application.DoEvents()

        Dim GameRow() As DataRow
        Dim GameID As Integer
        For Each kvp As KeyValuePair(Of String, String) In Clones
            newrow = FileDupes.NewRow
            GameRow = RecDT.Select("PrimaryFilename='" & kvp.Value & "'")
            If GameRow.Length > 0 Then
                GameID = GameRow(0).Item("ID")
                newrow("GamesID") = GameID
                newrow("Filename") = kvp.Key
                FileDupes.Rows.Add(newrow)
            Else
                Debug.WriteLine("Error: No Game with Primary Filename: " & kvp.Value)
            End If

            count = count + 1
            GameSetMatch.PB1.Value = count
            Application.DoEvents()

        Next

        Return True

    End Function

    Public Function TransferHyperSpin(HSDS As DataSet, RecDT As DataTable, FileDupes As DataTable) As Boolean
        Debug.WriteLine("Importing HyperList XML")
        Dim Clones As New Dictionary(Of String, String)
        Clones.Clear()

        Dim SystemRow() As DataRow
        Dim SystemID As Integer
        SystemRow = GameSetMatch._GSMDS.Tables("Systems").Select("SystemName='HyperSpin'")
        SystemID = SystemRow(0).Item("ID")

        Dim newrow As DataRow

        Dim count As Integer = 0
        GameSetMatch.OperationLB.Text = "Processing hyperlist.xml file...."
        GameSetMatch.PB1.Minimum = 0
        GameSetMatch.PB1.Maximum = HSDS.Tables("game").Rows.Count
        Application.DoEvents()


        For Each row As DataRow In HSDS.Tables("game").Rows
            Debug.WriteLine("Writing Name: {0} ; Filename: {1}",
                                row.Item("description"),
                                row.Item("name"))

            newrow = RecDT.NewRow

            newrow("GameName") = RemoveBracketedText(row.Item("name"), My.Settings.RemBracksGamename)
            newrow("PrimaryFilename") = RemoveBracketedText(row.Item("description"), My.Settings.RemBracksFilename)
            newrow("SafeGameName") = SafeFilename(RemoveBracketedText(row.Item("Name"), My.Settings.RemBracksGamename))

            newrow("SystemID") = SystemID
            RecDT.Rows.Add(newrow)
            count = count + 1
            GameSetMatch.PB1.Value = count
            Application.DoEvents()

        Next

        'transfer Dupes disctionary to datatable

        Return True

    End Function

    Public Function GetDataTable(sql As String) As DataTable

        Dim dt As New DataTable()

        Try

            Dim cnn As New SQLiteConnection("Data Source='C:\GameEx\DATA\EMULATORS\[Arcade] Sega Naomi.db3'")

            cnn.Open()

            Dim mycommand As New SQLiteCommand(cnn)

            mycommand.CommandText = sql

            Dim reader As SQLiteDataReader = mycommand.ExecuteReader()

            dt.Load(reader)

            reader.Close()


            cnn.Close()

            ' Catching exceptions is for communists

        Catch
        End Try

        Return dt

    End Function

    Public Shared Function ExecuteNonQuery(sql As String) As Integer

        Dim cnn As New SQLiteConnection("Data Source=C:CheckoutWorldDominator.s3db")

        cnn.Open()

        Dim mycommand As New SQLiteCommand(cnn)

        mycommand.CommandText = sql

        Dim rowsUpdated As Integer = mycommand.ExecuteNonQuery()

        cnn.Close()

        Return rowsUpdated

    End Function

    Public Shared Function ExecuteScalar(sql As String) As String

        Dim cnn As New SQLiteConnection("Data Source=C:CheckoutWorldDominator.s3db")

        cnn.Open()

        Dim mycommand As New SQLiteCommand(cnn)

        mycommand.CommandText = sql

        Dim value As Object = mycommand.ExecuteScalar()

        cnn.Close()

        If value IsNot Nothing Then


            Return value.ToString()
        End If

        Return ""

    End Function

    Private Function SafeFilename(filen As String) As String
        filen = filen.Replace(":", " -")
        For Each c In IO.Path.GetInvalidFileNameChars
            filen = filen.Replace(c, "")
        Next
        Return filen
    End Function



End Class
